package guoanmaker.operator.business.service;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.transaction.Transactional;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;


import guoanmaker.operator.business.model.OperatorCustomers;
import guoanmaker.operator.business.model.PersonalUser;
import guoanmaker.operator.business.model.Userview;

import guoanmaker.operator.business.repository.OperatorCustomersRepository;
import guoanmaker.operator.business.repository.PersonalUserRepository;

@Service
public class OperatorCustomersService implements OperatorCustomersServiceIntegerface{
	@Autowired
	private OperatorCustomersRepository operatorCustomersRepository;
	@Autowired
	private PersonalUserRepository personalUserRepository;
	@PersistenceContext
	EntityManager em;
	
	public Map doImportCustomerData(HttpServletRequest request, HttpServletResponse response, Workbook wb,
			MultipartFile dataFile,String peopleType) {
		Map<String, Object> map=new HashMap<String, Object>();
		Sheet sheet = wb.getSheetAt(0);
		int totcount = sheet.getLastRowNum();
		int count=0;
		List<OperatorCustomers> cus=new ArrayList<OperatorCustomers>();
				for(int i=0;i<totcount;i++){
					Row row = sheet.getRow(i + 1);
					OperatorCustomers customer=new OperatorCustomers();
					if(i==0){
						
					}
					String phone="";
					if(row.getCell(0)!=null){
						row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
						String mobiles=row.getCell(0).getStringCellValue().trim();
						Pattern p = Pattern.compile("^((13[0-9]{1})|(15[0-9]{1})|(18[0-9]{1})|(17[0-9]{1})|(14[0-9]{1}))\\d{8}$");  
						  
						Matcher m = p.matcher(mobiles); 
						boolean ss=m.matches();
						
						if(ss){
							 row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
					          phone=row.getCell(0).getStringCellValue().trim();
					          customer.setPhone(phone);
						}
						
				         
				       }
					
					if(row.getCell(1)!=null){
						row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
						String email=row.getCell(1).getStringCellValue().trim();
						Pattern p = Pattern.compile("^([a-z0-9A-Z]+[-|_|\\.]?)+[a-z0-9A-Z]@([a-z0-9A-Z]+(-[a-z0-9A-Z]+)?\\.)+[a-zA-Z]{2,}$");  
						  
						Matcher m = p.matcher(email); 
						boolean ss=m.matches();
						
						if(ss){
							 row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
							 customer.setEmail(row.getCell(1).getStringCellValue().trim());
					          
						}
						
				         
				       }
					customer.setPeopleType(peopleType);
					List<PersonalUser> S=personalUserRepository.viewmerUchantinfo(phone);
					
					if(S.size()!=0){
						
						cus.add(customer);
					count++;
					}
			        
					
				
				}
				System.out.println(cus+"cus");
				System.out.println(count+"count");
				System.out.println(totcount+"totcount");
				map.put("cus", cus);
				map.put("count", count);
				map.put("totcount", totcount);
				
				/*request.getSession().setAttribute("list", cus);*/
			return map;
	}
	
	/**
	 * 筛选客群
	 */
	public Map selectUserList(HttpServletRequest request, HttpServletResponse response,String peppleType,String resbeginTime,String resendTime,Integer dealnum ,Double money,Integer dealnumType,Integer moneyType){
		
		Map<String,Object> remap=new HashMap<String ,Object>();
		if(peppleType.equals("1")){
		String jpql="select u from Userview u where 1=1";
		
		if(resbeginTime!=null&&!resbeginTime.equals("")){ 
			jpql+="and u.resdate>='"+resbeginTime+"'";
		}
		if(resendTime!=null&&!resendTime.equals("")){
			jpql+="and u.resdate<='"+resendTime+"'";
		}
		if(dealnumType!=0){//1是交易量大于2小于 3等于
			if(dealnumType==1){
				jpql+="and u.dealnum>"+dealnum;
			}else if(dealnumType==2){
				jpql+="and u.dealnum<"+dealnum;
			}else if(dealnumType==3){
				jpql+="and u.dealnum="+dealnum;
			}
			
		}
		
		if(moneyType!=0){//1是交易量大于2小于 3等于
			if(moneyType==1){
				jpql+="and u.money>"+money;
			}else if(moneyType==2){
				jpql+="and u.money<"+money;
			}else if(moneyType==3){
				jpql+="and u.money="+money;
			}
			
		}
		
		Query query = em.createQuery(jpql);
		List<Userview> list = query.getResultList();
		/*request.getSession().setAttribute("list", list);*/
		remap.put("list", list);
		}else if(peppleType.equals("2")){
			String jpql="select b from Userview b where b.ifBusiness=2";
			
			if(resbeginTime!=null&&!resbeginTime.equals("")){ 
				jpql+="and b.busdate>='"+resbeginTime+"'";
			}
			if(resendTime!=null&&!resendTime.equals("")){
				jpql+="and b.busdate<='"+resendTime+"'";
			}
			if(dealnumType!=0){//交易量 1是大于2小于 3等于
				if(dealnumType==1){
					jpql+="and b.dealnum>"+dealnum;
				}else if(dealnumType==2){
					jpql+="and b.dealnum<"+dealnum;
				}else if(dealnumType==3){
					jpql+="and b.dealnum="+dealnum;
				}
				
			}
			
			if(moneyType!=0){//交易额 1是大于     2小于    3等于
				if(moneyType==1){
					jpql+="and b.money>"+money;
				}else if(moneyType==2){
					jpql+="and b.money<"+money;
				}else if(moneyType==3){
					jpql+="and b.money="+money;
				}
				
			}
			
			Query query = em.createQuery(jpql);
			List<Userview> list = query.getResultList();
			/*request.getSession().setAttribute("list", list);*/
			remap.put("list", list);
		}
		
		
	return remap;
	}
	public List<OperatorCustomers> selectCustomers(String messId){
		return operatorCustomersRepository.findBymessId(messId);
		
	}
	
	 @Transactional
	  public void delectAllBymessId(String messId){
		 String jpql="delete  from OperatorCustomers  u where u.messId=:messId";
		  Query query2=em.createQuery(jpql);//查询商品提供的服务区域
			query2.setParameter("messId", messId);
			
			query2.executeUpdate();
		  
	  }
	
}
